const oracledb = require('oracledb')

const xlsx = require('node-xlsx');
var fs = require('fs');

var moment = require('moment');

const DB_CONFIG = {
  user: 'cgxzcuser', 
  password: 'Tp3D1rBz#cx2024',
  //connectString: 'jdbc:oracle:thin:@172.50.100.181:1521:cgxzc' // 数据库地址：{IP:PORT/数据库名称}
  connectString: '172.50.100.181:1521/cgxzc' // 数据库地址：{IP:PORT/数据库名称}

  //  connectString: '172.50.100.169:1521/cgxzc',
  //         user: 'cgxzcuser_0522',
  //         password: 'cgxzcuser#2023'
}


console.log('运行模式:', oracledb.thin ? 'Thin' : 'Thick');

//oracledb.initOracleClient();
oracledb.initOracleClient({libDir: "C:\\Users\\15371\\scoop\\apps\\oracle-instant-client\\current"});

// oracledb.getConnection(DB_CONFIG).then( async connection => {
//   console.log('Connected successfully!');
//   // 执行 SQL 查询
//    result = await connection.execute(`SELECT * FROM AA_AARF`);
//       console.log('result:',result)
// }).catch(err => {
//   console.error('Connection error:', err);
// });

 const tableNames = [
    "CNJ_BOM_STEP_SETTING",
    "CUSTOMER_COMPARISON",
    "IL_ACCEPT_COMPANY",
    "IL_CRANE_MSG",
    "IL_STORE_PERSON",
    "MS_DEPT_MANAGEMENT",
    "PRODUCT_MAIN_CLASS",
    "SALE_DEPT",
    "SALE_POST",
    "SALE_USER_INFO",
    "SA_RECEIPT_AUTH",
    "SB_MATERIAL_FLOW_STORE_TYPE",
    "SB_PRIVATE_LINE",
    "SB_STATION",
    "SO_ADMIN_DISTRICT",
    "SO_COPRODUCT_SPEC",
    "SO_CUSTNO_DELIVERY",
    "SO_CUSTOMLINE",
    "SO_DEL_MATERIAL",
    "SO_DEL_MATERIAL_REQUIRE",
    "SO_DISTRICT",
    "SO_ORDER_FIELD_COMPARE",
    "SO_OUTSOURCE_SPEC",
    "SO_OUTSOURCE_STD",
    "SO_PLINE_SUPERVISE_SEND_USER",
    "SO_SALE_ORG_MAPPING",
   // "SO_SEARCH_TEMPLATE",
    "SO_SUPERVISE_COMPANY",
    "SO_SUPERVISE_PERSON",
    "SO_SUPERVISE_RELATION",
    "SO_TECH_REVIEWER_REL",
    "SS_INVOICE_AUTH",
    "SALE_DISTRICT",
    "SO_CUSTNO_RECEIVE"
];

//''
//@TGCXDB_88
//@CGXZCDB_ZSJ
const getSql = ()=>{
  //CGXZCUSER
    const sqlCount1 = tableNames.map(table=>`
      (
      select count(1)  from  (select * from  ${table}@TGCXDB_88 MINUS select * from ${table}@CGXZCDB_ZSJ) )   ${table}_88ToZSJ  `).reduce((a,b)=>a+','+b)


       const sqlCount2 = tableNames.map(table=>`
    (select count(1)  from  (select * from ${table}@CGXZCDB_ZSJ MINUS select * from  ${table}@TGCXDB_88   ) ) ${table}_ZSJTo88   `).reduce((a,b)=>a+','+b)
    return [
       `select ${sqlCount1} from dual`,
        `select ${sqlCount2} from dual`
    ]
}

const dbResult = {
  // '180':{
  //   dblink:'',
  //   result:[]
  // },
  '88':{
    dblink:'@TGCXDB_88',
    result:[]
  },
  'zsj':{
    dblink:'@CGXZCDB_ZSJ',
    result:[]
  }
}


// 使用连接池
async function run() {
  let pool;
  try {
    pool = await oracledb.createPool(DB_CONFIG);
    let connection;
    try {
      connection = await pool.getConnection();

      
        const sqls = getSql();
        console.log('sqls:',sqls)
          const result1 = await  connection.execute(sqls[0])
          const result2 = await  connection.execute(sqls[1])
         
      
          var buffer = xlsx.build([
                 {name:'统计',data:[
                  result1.metaData.map(s=>s.name),...result1.rows,
                   result2.metaData.map(s=>s.name),...result2.rows
                 ]},
            ]);
             fs.writeFileSync(`./统计差集.xlsx`,buffer,{'flag':'w'});


          // for(var table of tableNames){
          //   console.log('t：',table)
          //  const result = await  connection.execute(
          //     `
          //      select (
          //      select count(1)  from  (select * from  ${table}@TGCXDB_88 MINUS select * from ${table}@CGXZCDB_ZSJ)    ${table}_88ToZSJ 
          //      ) from dual
          //     `
          //  )
          // }



     
  
    } catch (err) {
      throw (err);
    } finally {
      if (connection) {
        try {
          await connection.close(); 
        } catch (err) {
          throw (err);
        }
      }
    }
  } catch (err) {
    console.log(err)
    throw err;
  } finally {
    await pool.close();
  }
}
run()